<?php
require_once("lib/db_connection.php");
$company_id = $_SESSION['company_id'];
$p1 = $_GET['p1'];
$p2 = $_GET['p2'];
$p3 = $_GET['p3'];

if($p2=='LIKE')
$passed_where = $p1." ".$p2." '%".$p3."%'";
if($p2!='LIKE')
$passed_where = $p1." ".$p2." '".$p3."'";

if($p1==''||$p2==''||$p3=='')
{
$query_veh = "SELECT 
vehicle_tbl.*,vehicle_tbl.chassis_num as c_num,vehicle_tbl.year as v_year,vehicle_tbl.customer as v_cust,
veh_purchase_tbl.*,veh_purchase_tbl.date as p_date,veh_purchase_tbl.price as p_price,veh_purchase_tbl.expiration_date as p_exp,
veh_engine_tbl.*,
veh_insurance_tbl.*,
veh_registration_tbl.*,veh_registration_tbl.date as r_date,
veh_depreciation_tbl.*,
veh_fluids_tbl.*,
gps_tbl.*,
veh_disposal_tbl.*,
veh_chassis_tbl.*,
veh_other_details_tbl.* 
FROM vehicle_tbl 
LEFT JOIN veh_purchase_tbl 
ON vehicle_tbl.vehicle_id=veh_purchase_tbl.vehicle_id
LEFT JOIN veh_engine_tbl 
ON vehicle_tbl.vehicle_id=veh_engine_tbl.vehicle_id
LEFT JOIN veh_insurance_tbl 
ON vehicle_tbl.vehicle_id=veh_insurance_tbl.vehicle_id
LEFT JOIN veh_registration_tbl 
ON vehicle_tbl.vehicle_id=veh_registration_tbl.vehicle_id
LEFT JOIN veh_depreciation_tbl 
ON vehicle_tbl.vehicle_id=veh_depreciation_tbl.vehicle_id
LEFT JOIN veh_fluids_tbl 
ON vehicle_tbl.vehicle_id=veh_fluids_tbl.vehicle_id
LEFT JOIN gps_tbl 
ON vehicle_tbl.vehicle_id=gps_tbl.vehicle_id
LEFT JOIN veh_disposal_tbl 
ON vehicle_tbl.vehicle_id=veh_disposal_tbl.vehicle_id
LEFT JOIN veh_chassis_tbl 
ON vehicle_tbl.vehicle_id=veh_chassis_tbl.vehicle_id
LEFT JOIN veh_other_details_tbl 
ON vehicle_tbl.vehicle_id=veh_other_details_tbl.vehicle_id
WHERE vehicle_tbl.company_id='".$company_id."'";
}

if($p1!=''||$p2!=''||$p3!='')
{
$query_veh = "SELECT 
vehicle_tbl.*,vehicle_tbl.chassis_num as c_num,vehicle_tbl.year as v_year,vehicle_tbl.customer as v_cust,
veh_purchase_tbl.*,veh_purchase_tbl.date as p_date,veh_purchase_tbl.price as p_price,veh_purchase_tbl.expiration_date as p_exp,
veh_engine_tbl.*,
veh_insurance_tbl.*,
veh_registration_tbl.*,veh_registration_tbl.date as r_date,
veh_depreciation_tbl.*,
veh_fluids_tbl.*,
gps_tbl.*,
veh_disposal_tbl.*,
veh_chassis_tbl.*,
veh_other_details_tbl.* 
FROM vehicle_tbl 
LEFT JOIN veh_purchase_tbl 
ON vehicle_tbl.vehicle_id=veh_purchase_tbl.vehicle_id
LEFT JOIN veh_engine_tbl 
ON vehicle_tbl.vehicle_id=veh_engine_tbl.vehicle_id
LEFT JOIN veh_insurance_tbl 
ON vehicle_tbl.vehicle_id=veh_insurance_tbl.vehicle_id
LEFT JOIN veh_registration_tbl 
ON vehicle_tbl.vehicle_id=veh_registration_tbl.vehicle_id
LEFT JOIN veh_depreciation_tbl 
ON vehicle_tbl.vehicle_id=veh_depreciation_tbl.vehicle_id
LEFT JOIN veh_fluids_tbl 
ON vehicle_tbl.vehicle_id=veh_fluids_tbl.vehicle_id
LEFT JOIN gps_tbl 
ON vehicle_tbl.vehicle_id=gps_tbl.vehicle_id
LEFT JOIN veh_disposal_tbl 
ON vehicle_tbl.vehicle_id=veh_disposal_tbl.vehicle_id
LEFT JOIN veh_chassis_tbl 
ON vehicle_tbl.vehicle_id=veh_chassis_tbl.vehicle_id
LEFT JOIN veh_other_details_tbl 
ON vehicle_tbl.vehicle_id=veh_other_details_tbl.vehicle_id
WHERE vehicle_tbl.company_id='".$company_id."'
AND ".$passed_where;	
}
$result_veh = mysql_query($query_veh)or die(mysql_error());
?>
<html>
<head>
<style type="text/css">
table,th,td
{
border:1.5px solid;
border-collapse:collapse;
}
</style>
<script type='text/javascript'>
function OpenExcel()
{
	var p1 = document.getElementById('p1').value;
	var p2 = document.getElementById('p2').value;
	var p3 = document.getElementById('p3').value;
	window.open('send_to_excel2.php?p1='+p1+'&p2='+p2+'&p3='+p3,'SendToExcel');
}
</script>
</head>
<body>
<table width='7100' style='margin-left:auto;margin-right:auto;font-size:12px;font-family:Times New Roman'>
<tr>
		<th width='1400' colspan='12' style="background-color:gray">MASTER FILE</th>
		<th width='400' colspan='4' style="background-color:gray">PURCHASE</th>
		<th width='900' colspan='8' style="background-color:gray">ENGINE</th>
		<th width='900' colspan='8' style="background-color:gray">CHASSIS</th>
		<th width='500' colspan='5' style="background-color:gray">FLUIDS</th>
		<th width='600' colspan='5' style="background-color:gray">LTO REGISTRATION</th>
		<th width='600' colspan='4' style="background-color:gray">DISPOSAL</th>
		<th width='400' colspan='4' style="background-color:gray">DEPRECIATION</th>
		<th width='300' colspan='3' style="background-color:gray">GPS</th>
		<th width='1100' colspan='11' style="background-color:gray">OTHER DETAILS</th>
</tr>
<tr>
		<!--VEHICLE 201-->
		<th width='116' rowspan='2'>Plate No</th>
		<th width='112' rowspan='2'>Year</th>
		<th width='116' rowspan='2'>Make</th>
		<th width='116' rowspan='2'>Model</th>
		<th width='120' rowspan='2'>Total Mileage</th>
		<th width='116' rowspan='2'>Ownership</th>
		<th width='116' rowspan='2'>Owners Name</th>
		<th width='116' rowspan='2'>Driver</th>
		<th width='116' rowspan='2'>Engine No</th>
		<th width='116' rowspan='2'>Chassis No</th>
		<th width='116' rowspan='2'>Department</th>
		<th width='116' rowspan='2'>Garage</th>
		
		<!--PURCHASE-->
		<th width='100' rowspan='2'>Purchase Date</th>
		<th width='100' rowspan='2'>Price</th>
		<th width='100' rowspan='2'>Purchase From</th>
		<th width=100' rowspan='2'>End of Warranty</th>
		
		<!--ENGINE-->
		<th width='200' rowspan='2'>Code</th>
		<th width='100' rowspan='2'>Power(bhp)</th>
		<th width='100' rowspan='2'>Torque(lb-ft)</th>
		<th width='100' rowspan='2'>Disp(ci)</th>
		<th width='100' rowspan='2'>Aircon Type</th>
		<th width='100' rowspan='2'>Aspiration</th>
		<th width='100' rowspan='2'>Cylinder</th>
		<th width='100' rowspan='2'>Fuel Type</th>
		
		<!--CHASSIS-->
		<th width='100' rowspan='2'>Diff Type</th>
		<th width='100' rowspan='2'>Trans Type</th>
		<th width='100' rowspan='2'>Trans Code</th>
		<th width='100' rowspan='2'>Ratio</th>
		<th width='200' colspan='2'>Front</th>
		<th width='200' colspan='2'>Rear</th>
		
		<!--FLUIDS-->
		<th width='100' rowspan='2'>Oil Type</th>
		<th width='100' rowspan='2'>Transmission Fluid</th>
		<th width='100' rowspan='2'>Freon</th>
		<th width='100' rowspan='2'>Gear Oil</th>
		<th width='100' rowspan='2'>Hydraulic Fluid</th>
		
		<!--LTO REGISTRATION-->
		<th width='200' rowspan='2'>Field Office</th>
		<th width='100' rowspan='2'>CR No.</th>
		<th width='100' rowspan='2'>Regitration Date</th>
		<th width='100' rowspan='2'>OR No.</th>
		<th width='100' rowspan='2'>OR Date</th>
		
		<!--DISPOSAL-->
		<th width='100' rowspan='2'>Date</th>
		<th width='100' rowspan='2'>Sold to</th>
		<th width='200' rowspan='2'>Address</th>
		<th width='100' rowspan='2'>Price</th>
		<!--<th width='100' rowspan='2'>Odometer</th>-->
		
		<!--DEPRECIATION-->
		<th width='100' rowspan='2'>Description</th>
		<th width='100' rowspan='2'>Total Period</th>
		<th width='100' rowspan='2'>Current Value</th>
		<th width='100' rowspan='2'>Depreciation per period</th>
		
		<!--GPS-->
		<th width='100' rowspan='2'>ID</th>
		<th width='100' rowspan='2'>Sim No</th>
		<th width='100' rowspan='2'>Device</th>
		
		<!--OTHER DETAILS-->
		<th width='100' rowspan='2'>w/ Fleet Card</th>
		<th width='100' rowspan='2'>ID No</th>
		<th width='100' rowspan='2'>Band</th>
		<th width='100' rowspan='2'>Benefit Plan</th>
		<th width='100' rowspan='2'>Cost Center</th>
		<th width='100' rowspan='2'>Group</th>
		<th width='100' rowspan='2'>Division</th>
		<th width='100' rowspan='2'>Section</th>
		<th width='100' rowspan='2'>Ref PO</th>
		<th width='100' rowspan='2'>Asset No</th>
		<th width='100' rowspan='2'>Equipt No</th>
</tr>
<tr>
		<th width='100'>Wheel Size</th>
 		<th width='100'>Tire Size</th>
 		<th width='100'>Wheel Size</th>
 		<th width='100'>Tire Size</th>
</tr>
		
		<?php
		while($row_veh = mysql_fetch_array($result_veh))
		{
			//TRACKING
			$tracking = $row_veh['track_by'];
			if($tracking=='distance')
			$curr_od = $row_veh['curr_tracking_value'].'Km';
			if($tracking=='hours')
			$curr_od = $row_veh['curr_hours'].'Hrs';
			
			//FLEET CARD
			$f_card = $row_veh['fleet_card'];
			if($f_card==1)
			$fcard = 'Yes';
			if($f_card==0)
			$fcard = 'No';
			if($f_card==null)
			$fcard = ' ';
			
			//DEPARTMENT
			$query_dept = "SELECT * FROM lookup_personnel_tbl WHERE company_id='".$company_id."' and id='".$row_veh['department']."'";
			$result_dept = mysql_query($query_dept)or die(mysql_error());
			$row_dept = mysql_fetch_array($result_dept);
			
			//FUEL
			$query_fuel = "SELECT * FROM lookup_fuel_tbl WHERE company_id='".$company_id."' and id='".$row_veh['fuel_type']."'";
			$result_fuel = mysql_query($query_fuel)or die(mysql_error());
			$row_fuel = mysql_fetch_array($result_fuel);
			
			//FRONT WHEEL
			$query_fwheel = "SELECT * FROM lookup_tire_tbl WHERE company_id='".$company_id."' and id='".$row_veh['fwheel_size']."' and category='wheel sizes'";
			$result_fwheel = mysql_query($query_fwheel)or die(mysql_error());
			$row_fwheel = mysql_fetch_array($result_fwheel);
			
			//REAR WHEEL
			$query_rwheel = "SELECT * FROM lookup_tire_tbl WHERE company_id='".$company_id."' and id='".$row_veh['rwheel_size']."' and category='wheel sizes'";
			$result_rwheel = mysql_query($query_rwheel)or die(mysql_error());
			$row_rwheel = mysql_fetch_array($result_rwheel);
			
			//FRONT TIRE
			$query_ftire = "SELECT * FROM lookup_tire_tbl WHERE company_id='".$company_id."' and id='".$row_veh['ftire_size']."' and category='sizes'";
			$result_ftire = mysql_query($query_ftire)or die(mysql_error());
			$row_ftire = mysql_fetch_array($result_ftire);
			
			//REAR TIRE
			$query_rtire = "SELECT * FROM lookup_tire_tbl WHERE company_id='".$company_id."' and id='".$row_veh['rtire_size']."' and category='sizes'";
			$result_rtire = mysql_query($query_rtire)or die(mysql_error());
			$row_rtire = mysql_fetch_array($result_rtire);
			
		?>
		<tr>
			<!--VEHICLE 201-->
			<td style="text-align:center;">&nbsp;<?php echo $row_veh["veh_num"] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh["v_year"] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh["manufacturer_name"] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh["model_name"] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $curr_od ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['ownership'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['customer'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['operator'] ?>&nbsp;</td>
		    <td style="text-align:center;">&nbsp;<?php echo $row_veh['engine_model'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['c_num'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_dept['value'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['location'] ?>&nbsp;</td>
			
			<!--PURCHASE-->
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['p_date'] ?>&nbsp;</td>
			<td style="text-align:right;">&nbsp;<?php echo $row_veh['p_price'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['purchase_from'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['p_exp'] ?>&nbsp;</td>
			
			<!--ENGINE-->
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['code'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['power_bhp'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['torque_lb'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['disp_ci'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['aircon_type'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['aspiration'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['cylinder'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_fuel['value'] ?>&nbsp;</td>
			
			<!--CHASSIS-->
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['diff_type'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['trans_type'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['trans_code'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['ratio'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_fwheel['value'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_rtire['value'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_rwheel['value'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_rtire['value'] ?>&nbsp;</td>
			
			<!--FLUID-->
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['oil_type'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['transmission_fluid'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['freon'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['gear_oil'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['hydraulic_fluid'] ?>&nbsp;</td>
			
			<!--REGISTRATION-->
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['field_office'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['cr_no'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['r_date'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['or_no'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['or_date'] ?>&nbsp;</td>
			
			<!--DISPOSAL-->
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['sales_date'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['buyer'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['bldg'].' '.$row_veh['street'].' '.$row_veh['city'].','.$row_veh['country'] ?>&nbsp;</td>
			<td style="text-align:right;">&nbsp;<?php echo $row_veh['price'] ?>&nbsp;</td>
			<!--<td style="text-align:right;">&nbsp;<?php echo $row_veh['meter'] ?>&nbsp;</td>-->
			
			<!--DEPRECIATION-->
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['description'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['tot_period'] ?>&nbsp;</td>
			<td style="text-align:right;">&nbsp;<?php echo $row_veh['curr_value'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['dep_per_period'] ?>&nbsp;</td>
			
			<!--GPS-->
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['gps_id'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['sim_num'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['device'] ?>&nbsp;</td>
			
			<!--OTHER DETAILS-->
			<td style="text-align:center;">&nbsp;<?php echo $fcard ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['id_no'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['band'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['benefit_plan'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['cost_center'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['group1'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['division'] ?>&nbsp;</td>
		    <td style="text-align:center;">&nbsp;<?php echo $row_veh['section'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['ref_po'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['asset_no'] ?>&nbsp;</td>
			<td style="text-align:center;">&nbsp;<?php echo $row_veh['equipt_no'] ?>&nbsp;</td>
		</tr>
		<?php
		}
		?>
</table>
<br />
<input id="p1" type="hidden" value='<?php echo $p1 ?>' />
<input id="p2" type="hidden" value='<?php echo $p2 ?>' />
<input id="p3" type="hidden" value='<?php echo $p3 ?>' />
<input type="submit" value="Send to Excel" style='margin-left:auto;margin-right:auto;font-size:12px;font-family:Times New Roman' onClick=OpenExcel() />
</body>
</html>
		
